﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Threading;
using System.Diagnostics;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Runtime.Serialization;
using Microsoft.Win32;
using System.Management;


namespace Etltools
{
   
    public partial class FrmMain : Form
    {
        const string MESSAGE_TXT = "您签退了吗？";
        const string MESSAGE_TITLE = "提示";

        IList<Task> lstTask = new List<Task>();
        IList<Station> lstStation = new List<Station>();
        IList<Task> lstTask2 = new List<Task>();
        public FrmMain()
        {
            initializeSystemEvnets();
            InitializeComponent();
        }

        private void initializeSystemEvnets()
        {
            SystemEvents.PowerModeChanged += new PowerModeChangedEventHandler(SystemEvents_PowerModeChanged);
            SystemEvents.SessionEnding += new SessionEndingEventHandler(SystemEvents_SessionEnding);
        }

        void SystemEvents_SessionEnding(object sender, SessionEndingEventArgs e)
        {
            e.Cancel = true;
        }

        void SystemEvents_PowerModeChanged(object sender, PowerModeChangedEventArgs e)
        {
            if (e.Mode == PowerModes.StatusChange)
            {
                Debug.WriteLine(e.ToString());
            }
            
        }

        private void btn_proce_Click(object sender, EventArgs e)
        {
            if (lstTask.Count == 0)
            {
                MessageBox.Show("请先获取原数据...");
                return;
            }
            lstTask2 = lstTask2.OrderBy(m => m.id).ToList();
            XDocument xdoc = new XDocument(new XDeclaration("1.0", "GB2312", null));
            XElement element = new XElement("ColRefInfo");
            foreach (var v in lstTask2)
            {
                element.Add(new XElement("Task",
                    new XElement("TaskID", v.id),
                    new XElement("DstTable", v.name),
                    from a in v.fileds
                    let pk = v.PrimaryKey.Split('|').Contains(a) ? "1" : "0"
                    select new XElement("Columns",
                        new XAttribute("SrcCol",a),
                        new XAttribute("DstCol",a),
                        new XAttribute("PK",pk))));
            }
            xdoc.Add(element);
            xdoc.Save("ColumnsReference.xml");

            xdoc = new XDocument(new XDeclaration("1.0", "GB2312", null));
            element = new XElement("TaskInfo");
            foreach (var v in lstTask2)
            {
                string sql = "";
                string act = "";
                string hasparams = "";
                if (string.IsNullOrEmpty(v.ParamField))
                {
                    sql = "select * from " + v.name;
                    act = "DELADD";
                    hasparams = "0";
                }
                else
                {
                    if (v.StepType.ToLower() == "max")
                    {
                        act = "UPDATE";
                        hasparams = "0";
                        sql = string.Format("select * from {1} order by {2} desc rows 1 to {0}", v.Step, v.name, v.PrimaryKey.Split('|')[1]);
                    }
                    else
                    {
                        act = "ADD";
                        hasparams = "1";
                        sql = string.Format("select * from {0} where {1} between '{2}' and '{3}'", v.name, v.ParamField, "{0}", "{1}");
                    }
                    
                }
                element.Add(new XElement("Task",
                    new XElement("TaskID", v.id),
                    new XElement("TaskName",v.name),
                    new XElement("SQLStatement",sql),
                    new XElement("Action", act),
                    new XElement("DataSource", "IB"),
                    new XElement("HaveParams", hasparams),
                    new XElement("Step", v.Step, new XAttribute("StepType", v.StepType)),
                    new XElement("PrimaryKey",v.PrimaryKey)
                    ));
            }
            xdoc.Add(element);
            xdoc.Save("TaskInfo.xml");



            string destip = string.Format("Data Source={0};Initial Catalog=OilStationDataTrans;User ID={1};Password={2}",
                tb_ip.Text,
                tb_username.Text,
                tb_password.Text);
            xdoc = new XDocument(new XDeclaration("1.0", "GB2312", null));
            element = new XElement("StationInfo");
            foreach (var v in lstStation)
            {
                string stribip = @"DriverName=Interbase;Database={0}:{1};RoleName=RoleName;User_Name=sysdba;Password=masterkey;SQLDialect=3;MetaDataAssemblyLoader=Borland.Data.TDBXInterbaseMetaDataCommandFactory,Borland.Data.DbxReadOnlyMetaData,Version=11.0.5000.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b;GetDriverFunc=getSQLDriverINTERBASE;LibraryName=dbxint30.dll;VendorLib=GDS32.DLL";
                element.Add(new XElement("Station",
                    new XElement("StationID",v.id),
                    new XElement("StationName",v.name),
                    new XElement("IsActive",1),
                    new XElement("InterBaseIP",stribip),
                    new XElement("MySQLIP",null),
                    new XElement("DestIP",destip),
                    new XElement("Interval","30"),
                    from a in lstTask2
                    let start = get_start_end(a).Split('|')[0]
                    let end = get_start_end(a).Split('|')[1]
                    select new XElement("TaskInfo",
                        new XElement("TaskID",a.id),
                        new XElement("StartTime", start),
                        new XElement("EndTime", end))));
            }
            xdoc.Add(element);
            xdoc.Save("StationTaskRunInfo.xml");

            MessageBox.Show("生成XML文件成功。");

        }

        private string get_start_end(Task t)
        {
            string startime = this.dateTimePicker1.Value.Date.ToString().Replace('/','-');
            string endtime = this.dateTimePicker1.Value.AddHours(24).Date.ToString().Replace('/', '-');
            
            if (t.StepType.ToLower() == "datetime")
            {
                return startime + "|" + endtime;
            }
            if (t.StepType.ToLower() == "int")
            {
                return "0|" + t.Step.ToString();
            }
            if (t.StepType.ToLower() == "max")
            {
                return "0|0";
            }
            return "0|0";
        }

        private void GetStationInfo()
        {
            string strCon = string.Format("Data Source={0};Initial Catalog=OilStationDataTrans;User ID={1};Password={2}",
                tb_ip.Text,
                tb_username.Text,
                tb_password.Text);
            IDbConnection conn = new SqlConnection(strCon);
            
            IDbCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select sys_code as id,station_name as name,ip_add as ibip from v_station_info";
            conn.Open();
            IDataReader reader = cmd.ExecuteReader();
            //生成加油站信息
            while (reader.Read())
            {
                lstStation.Add(new Station
                {
                    id = reader["id"].ToString(),
                    name = reader["name"].ToString(),
                    ibip = reader["ibip"].ToString()
                });
            }
            reader.Close();
            conn.Close();

            cmd.CommandText = "select table_name as 数据表,table_para as 抽取参数,table_para_type as 参数类型,table_step_len as 步长,table_pk_col 主键列 from own_trans_table";
            cmd.Connection = conn;
            conn.Open();
            reader = cmd.ExecuteReader();

            int taskid = 1;
            while (reader.Read())
            {
                var task = lstTask.Where(x => x.name.ToLower() == reader["数据表"].ToString().ToLower().Trim()).FirstOrDefault();
                if (task == null)
                {
                    continue;
                }
                task.PrimaryKey = reader["主键列"].ToString();
                try
                {
                    task.ParamField = reader["抽取参数"].ToString();
                    task.Step = Convert.ToInt32(reader["步长"]);
                    task.StepType = reader["参数类型"].ToString();
                }
                catch (System.InvalidCastException ex)
                {
                    task.ParamField = "";
                    task.Step = 0;
                    task.StepType = "";
                    Debug.WriteLine(ex.Message);
                }
                Task tempt = (Task)task.Clone();
                tempt.id = taskid++;
                lstTask2.Add(tempt);
            }
            reader.Close();
            conn.Close();
            reader.Dispose();
            cmd.Dispose();
            conn.Dispose();
        }

        private void GetTableSchema()
        {
//            SELECT   TABLE_NAME,COLUMN_NAME   FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE   
//WHERE   TABLE_NAME= 'DAILYPLU'

            SqlConnection c = new SqlConnection(string.Format("Data Source={0};Initial Catalog=OilStationDataTrans;User ID={1};Password={2}",
                tb_ip.Text,
                tb_username.Text,
                tb_password.Text));
            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.Connection = c;
            sqlcmd.CommandText = "select id,name from sysobjects where xtype='U'";
            c.Open();
            SqlDataReader reader = sqlcmd.ExecuteReader(CommandBehavior.Default);
            //取抽取系统表，排除以OWN_开头的表
            while (reader.Read())
            {
                if (!reader["name"].ToString().ToLower().StartsWith("own_"))
                    lstTask.Add(new Task
                    {
                        id = (int)reader["id"],
                        name = reader["name"].ToString(),
                        fileds = new List<string>()
                    });
            }
            int i = 0;
            //生成字段
            foreach (var t in lstTask)
            {
                sqlcmd.CommandText = "select name from syscolumns where id=" + t.id.ToString();
                reader.Close();
                reader = sqlcmd.ExecuteReader();
                while (reader.Read())
                {
                    t.fileds.Add(reader.GetValue(0).ToString());
                }
                t.id = ++i;
            }
            c.Close();
            sqlcmd.Dispose();
        }

        private void btn_compare_Click(object sender, EventArgs e)
        {
            SelectQuery selectQuery = new SelectQuery("select * from Win32_Processor");
            ManagementObjectSearcher searcher = new ManagementObjectSearcher(selectQuery);
            foreach (ManagementObject disk in searcher.Get())
            {
                //获取驱动器盘符
                Debug.WriteLine(disk["CurrentClockSpeed"].ToString());
            }

            FrmCmp frmcmp = new FrmCmp();
            frmcmp.Tag = lstStation;
            frmcmp.strLocalIP = tb_ip.Text;
            frmcmp.username = tb_username.Text;
            frmcmp.password = tb_password.Text;
            frmcmp.Show();
        }

        private void FrmMain_Load(object sender, EventArgs e)
        {
           
        }

        private void btn_BatchUpdate_Click(object sender, EventArgs e)
        {

        }

        private void FrmMain_FormClosing(object sender, FormClosingEventArgs e)
        {
            //e.Cancel = true;
        }

        private void btn_getData_Click(object sender, EventArgs e)
        {
            lstTask.Clear();
            lstStation.Clear();
            GetTableSchema();
            GetStationInfo();
        }
    }

    [Serializable]
    class Task:ICloneable
    {
        /// <summary>
        /// 任务ID
        /// </summary>
        public int id { get; set; }
        /// <summary>
        /// 任务表名
        /// </summary>
        public string name { get; set; }
        /// <summary>
        /// 任务字段名
        /// </summary>
        public IList<string> fileds { get; set; }
        /// <summary>
        /// 参数字段名
        /// </summary>
        public string ParamField { get; set; }
        /// <summary>
        /// 步进长度
        /// </summary>
        public int Step { get; set; }
        /// <summary>
        /// 步进类型
        /// </summary>
        public string StepType { get; set; }
        /// <summary>
        /// 主键名称
        /// </summary>
        public string PrimaryKey { get; set; }

        #region ICloneable 成员

        public object Clone()
        {
            using (Stream objectStream = new MemoryStream())
            {
                IFormatter formatter = new BinaryFormatter();
                formatter.Serialize(objectStream, this);
                objectStream.Seek(0, SeekOrigin.Begin);
                return formatter.Deserialize(objectStream);
            }
        }

        #endregion
    }

    class Station
    {
        /// <summary>
        /// 加油站编码
        /// </summary>
        public string id { get; set; }
        /// <summary>
        /// 加油站名称
        /// </summary>
        public string name { get; set; }
        /// <summary>
        /// 源IB数据库IP
        /// </summary>
        public string ibip { get; set; }
    }
}
